﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using COMExcel = Microsoft.Office.Interop.Excel;
using BLL;
using Public;
//using System.Drawing.Printing.PrintingPermissionLevel;
using Microsoft.Office.Core;


namespace QLNhanKhau
{
    public partial class frmNK15 : Form
    {
        public frmNK15()
        {
            InitializeComponent();
        }
        public static void DisplayExcel(DateTime batdau, DateTime ketthuc,bool print)
        {
            COMExcel.Application exApp= new COMExcel.Application();

            //COMExcel.Workbook exBook = exApp.Workbooks.Add(
            //COMExcel.XlWBATemplate.xlWBATWorksheet );      

            //them file có sẵn

            string workbookPath = "C:/Users/MINHPHUONG/Desktop/qlnk-project/BaoCaoTongHop.xlsx";

            COMExcel._Workbook exBook = exApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, COMExcel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);

            COMExcel._Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
            
            exSheet.Activate();
            exSheet.Name = "Báo cáo tổng hợp";


            //< ="-" ="text/; =utf-8">< name="ProgId" ="OneNote.">< name="Generator" ="Microsoft OneNote 11">

            // Range là ô [1,1] (A1)
            COMExcel.Range r1 = (COMExcel.Range)exSheet.Cells[2, 1];
            r1.Value2 = "CÔNG AN HUYỆN BẾN LỨC";
            COMExcel.Range r2 = (COMExcel.Range)exSheet.Cells[3, 1];
            r2.Value2 = "CÔNG AN XÃ "+ThietLapBLL.LoadDiaPhuong();

            COMExcel.Range rIdatetu = (COMExcel.Range)exSheet.Cells[3, 36];
            rIdatetu.Value2 = batdau;
            COMExcel.Range rIdateden = (COMExcel.Range)exSheet.Cells[3, 45];
            rIdateden.Value2 = ketthuc;
            COMExcel.Range rI2 = (COMExcel.Range) exSheet.Cells[4, 36];
            // Ghi dữ liệu
            rI2.Value2 = NhanKhauBLL.CountNhanKhau();
            COMExcel.Range rI1 = (COMExcel.Range)exSheet.Cells[4, 30];
            rI1.Value2 = SoHoKhauBLL.CountSoHoKhau();
            COMExcel.Range rI3 = (COMExcel.Range)exSheet.Cells[5, 42];
            rI3.Value2 = NhanKhauBLL.CountNhanKhauNu();
            COMExcel.Range rI4 = (COMExcel.Range)exSheet.Cells[5, 51];
            rI4.Value2 = NhanKhauBLL.CountNK14();

            //phần II

            COMExcel.Range rII1 = (COMExcel.Range)exSheet.Cells[12, 1];
            rII1.Value2 = SoHoKhauBLL.CountSoHoKhauByDay(batdau, ketthuc);
            COMExcel.Range rII2 = (COMExcel.Range)exSheet.Cells[12, 9];
            rII2.Value2 = NhanKhauBLL.CountNKByDay(batdau, ketthuc);
            //thanh thị chua làm
            COMExcel.Range rII4 = (COMExcel.Range)exSheet.Cells[12, 25];
            rII4.Value2 = NhanKhauBLL.CountNKNuByDay(batdau, ketthuc);
            COMExcel.Range rII5 = (COMExcel.Range)exSheet.Cells[12, 32];
            rII5.Value2 = NhanKhauBLL.CountNKNuByDay(batdau, ketthuc);
            COMExcel.Range rII7 = (COMExcel.Range)exSheet.Cells[12, 50];
            rII7.Value2 = ChuyenKhauBLL.CountChuyenKhauNKByDay(batdau, ketthuc);
            COMExcel.Range rII9 = (COMExcel.Range)exSheet.Cells[12, 65];
            rII9.Value2 = ChuyenKhauBLL.CountChuyenKhauNKNuByDay(batdau, ketthuc);
            COMExcel.Range rII10 = (COMExcel.Range)exSheet.Cells[12, 73];
            rII10.Value2 = ChuyenKhauBLL.CountChuyenKhauNK14ByDay(batdau, ketthuc);
            COMExcel.Range rII12 = (COMExcel.Range)exSheet.Cells[20, 5];
            rII12.Value2 = ChuyenKhauBLL.CountChuyenKhauNKTinhByDay(batdau, ketthuc);
            COMExcel.Range rII14 = (COMExcel.Range)exSheet.Cells[20, 15];
            rII14.Value2 = ChuyenKhauBLL.CountChuyenKhauNKTinhNuByDay(batdau, ketthuc);
            COMExcel.Range rII15 = (COMExcel.Range)exSheet.Cells[20, 21];
            rII15.Value2 = ChuyenKhauBLL.CountChuyenKhauNKTinh14ByDay(batdau, ketthuc);
            COMExcel.Range rII17 = (COMExcel.Range)exSheet.Cells[20, 32];
            rII17.Value2 = ChuyenKhauBLL.CountChuyenKhauNKHuyenByDay(batdau, ketthuc);
            COMExcel.Range rII19 = (COMExcel.Range)exSheet.Cells[20, 42];
            rII19.Value2 = ChuyenKhauBLL.CountChuyenKhauNKHuyenNuByDay(batdau, ketthuc);
            COMExcel.Range rII20 = (COMExcel.Range)exSheet.Cells[20, 48];
            rII20.Value2 = ChuyenKhauBLL.CountChuyenKhauNKHuyen14ByDay(batdau, ketthuc);
            COMExcel.Range rII22 = (COMExcel.Range)exSheet.Cells[20, 60];
            rII22.Value2 = ChuyenKhauBLL.CountChuyenKhauNKXaByDay(batdau, ketthuc);
            COMExcel.Range rII24 = (COMExcel.Range)exSheet.Cells[20, 70];
            rII24.Value2 = ChuyenKhauBLL.CountChuyenKhauNKXaNuByDay(batdau, ketthuc);
            COMExcel.Range rII25 = (COMExcel.Range)exSheet.Cells[20, 76];
            rII25.Value2 = ChuyenKhauBLL.CountChuyenKhauNKXa14ByDay(batdau, ketthuc);
            //phần 3
            COMExcel.Range rIII3 = (COMExcel.Range)exSheet.Cells[41, 11];
            rIII3.Value2 = SoHoKhauBLL.CountSoHoKhauByDay(batdau, ketthuc);
            COMExcel.Range rIII7 = (COMExcel.Range)exSheet.Cells[41, 32];
            rIII7.Value2 = ChuyenKhauBLL.CountCKNgoaiTinhByDay(batdau, ketthuc);
            COMExcel.Range rIII8 = (COMExcel.Range)exSheet.Cells[41, 37];
            rIII8.Value2 = KhaiSinhBLL.CountKhaiSinhByDay(batdau, ketthuc);
            COMExcel.Range rIII9 = (COMExcel.Range)exSheet.Cells[41, 42];
            rIII9.Value2 = NhanKhauBLL.CountNKNuocNgoaiVeByDay(batdau, ketthuc);
            COMExcel.Range rIII10 = (COMExcel.Range)exSheet.Cells[41, 47];
            rIII10.Value2 = SoHoKhauBLL.CountSHKDeleteByDay(batdau, ketthuc);
            COMExcel.Range rIII11 = (COMExcel.Range)exSheet.Cells[41, 51];
            rIII11.Value2 = NhanKhauBLL.CountNKDeleteByDay(batdau, ketthuc);
            COMExcel.Range rIII12 = (COMExcel.Range)exSheet.Cells[41, 55];
            rIII12.Value2 = NhanKhauBLL.CountNKDieByDay(batdau, ketthuc);
            COMExcel.Range rIII13 = (COMExcel.Range)exSheet.Cells[41, 59];
            rIII13.Value2 = NhanKhauBLL.CountNKTuyenDungByDay(batdau, ketthuc);
            COMExcel.Range rIII14 = (COMExcel.Range)exSheet.Cells[41, 64];
            rIII14.Value2 = NhanKhauBLL.CountNKHuyDKByDay(batdau, ketthuc);
            COMExcel.Range rIII15 = (COMExcel.Range)exSheet.Cells[41, 70];
            rIII15.Value2 = NhanKhauBLL.CountNKDiNuocNgoaiByDay(batdau, ketthuc);
            COMExcel.Range rIII16 = (COMExcel.Range)exSheet.Cells[41, 76];
            rIII16.Value2 = ChuyenKhauBLL.CountChuyenDiByDay(batdau, ketthuc);
            COMExcel.Range rIII17 = (COMExcel.Range)exSheet.Cells[48, 1];
            rIII17.Value2 = ChuyenKhauBLL.CountSoCKByDay(batdau, ketthuc);
            COMExcel.Range rIII18 = (COMExcel.Range)exSheet.Cells[48, 6];
            rIII18.Value2 = ChuyenKhauBLL.CountChuyenDiByDay(batdau, ketthuc);
            COMExcel.Range rIII19 = (COMExcel.Range)exSheet.Cells[48, 11];
            rIII19.Value2 = ChuyenKhauBLL.CountTachKhauByDay(batdau, ketthuc);
            //phần 4

            COMExcel.Range rIV14 = (COMExcel.Range)exSheet.Cells[67, 7];
            rIV14.Value2 = frmDangNhap.user_login.HoTen;
            COMExcel.Range r99 = (COMExcel.Range)exSheet.Cells[64, 53];
            r99.Value2 = ThietLapBLL.LoadDiaPhuong().ToString()+", ngày "+DateTime.Now.Day+", tháng "+DateTime.Now.Month+", năm "+DateTime.Now.Year;
            COMExcel.Range r98 = (COMExcel.Range)exSheet.Cells[65, 53];
            r98.Value2 = "TRƯỞNG CÔNG AN XÃ "+ThietLapBLL.LoadDiaPhuong().ToString();

            // Đóng chương trình excel
            
            // Giãn cột
            r1.Columns.AutoFit();
            // Hiển thị chương trình excel
            
            // Đóng chương trình excel
            
            Console.WriteLine("Wait to excel.exe");
            Console.ReadLine();
            //exBook.PrintPreview();
            if (print == true)
            {
                exApp.Visible = false;
                exBook.PrintOutEx();
                exApp.Quit();
            }
            //// Ẩn chương trình
            //exApp.Visible = false;    
            else
            {
                //Save file
                
                exBook.SaveAs("E:\\" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day +"-"+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+ ".xls", COMExcel.XlFileFormat.xlWorkbookNormal,
                                null, null,false, false,
                                COMExcel.XlSaveAsAccessMode.xlExclusive,
                                false, false, false, false, false);
                //exBook.SaveAs(
               
                exApp.Visible = true;
                exApp.EditDirectlyInCell = false;
                
                //exApp.Quit();
            }
            //Prevents screen refreshing.

            //exBook.Close(false, false, false);
            //exApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            //DateTime batdau = dateBatDau.Value;
            
            DisplayExcel(dateBatDau.Value,dateKetThuc.Value,true);
            this.Close();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            DisplayExcel(dateBatDau.Value, dateKetThuc.Value, false);
            this.Close();
        }   
    }
}
